/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package workforce.limousine.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.sql.DataSource;
import limousine.vo.Car;

/**
 *
 * @author Tailer
 */
public class CarService implements Service {

    private DataSource ds = null;
    private int limit = 20;

    public DataSource getDs() {
        return ds;
    }

    public void setDs(DataSource ds) {
        this.ds = ds;
    }

    @Override
    public Object findById(String uid, String id) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public boolean removeAll(String[] ids) {
        throw new UnsupportedOperationException("Not supported yet.");
    }

    public int getLimit() {
        return limit;
    }

    @Override
    public void setDataSource(DataSource ds) {
        this.ds = ds;
    }

    public void setLimit(int limit) {
        this.limit = limit;
    }

    public List search(int offset, Object[] certiria) {
        System.out.println("CarService search...");
        List<Car> list = new ArrayList<Car>();
        Connection conn = null;
        try {
            conn = ds.getConnection();
            StringBuilder s = new StringBuilder();
            s.append("SELECT ");
            s.append("IFNULL(A.CAR_ID, '-') AS CAR_ID,");
            s.append("IFNULL(A.CAR_NAME, '-') AS CAR_NAME,");
            s.append("IFNULL(A.CAR_NO, '-') AS CAR_NO,");
            s.append("IFNULL(A.CAR_TYPE, 0) AS CAR_TYPE,");
            s.append("IFNULL(A.STATUS, 0) AS STATUS,");
            s.append("IFNULL(A.CREATE_BY, '-') AS CREATE_BY,");
            s.append("IFNULL(A.CREATE_DATE, CURRENT_TIMESTAMP) AS CREATE_DATE,");
            s.append("IFNULL(A.UPDATE_BY, '-') AS UPDATE_BY,");
            s.append("IFNULL(A.UPDATE_DATE, CURRENT_TIMESTAMP) AS UPDATE_DATE, ");
            s.append("(SELECT IFNULL(B.LOOKUP_NAME, '-') FROM lookup B WHERE B.LOOKUP_VALUE = A.CAR_TYPE AND B.LOOKUP_GROUP_ID = 'CAR_TYPE' ) AS CAR_TYPE_NAME,");
s.append("(SELECT IFNULL(B.LOOKUP_NAME, '-') FROM lookup B WHERE B.LOOKUP_VALUE = A.STATUS AND B.LOOKUP_GROUP_ID = 'STATUS' ) AS STATUS_NAME,");
s.append("(SELECT IFNULL(B.USER_NAME, '-') FROM users B WHERE B.id = A.CREATE_BY ) AS CREATE_BY_NAME, ");
s.append("(SELECT IFNULL(B.USER_NAME, '-') FROM users B WHERE B.id = A.UPDATE_BY ) AS UPDATE_BY_NAME ");
            s.append("FROM car A WHERE A.CAR_NAME LIKE ? ");
            s.append("LIMIT ? OFFSET ?");
            System.out.println("sql[" + s.toString() + "]");
            PreparedStatement stmt = conn.prepareStatement(s.toString());
            String name = (String) certiria[0];
            name = (name != null) ? name.replace('*', '%') : "";
            if (name.indexOf("%") == -1) {
                name = "%" + name + "%";
            }
            stmt.setString(1, name);
            stmt.setInt(2, limit);
            stmt.setInt(3, offset);
            ResultSet rs = stmt.executeQuery();
            Car o = null;
            while (rs.next()) {
                o = new Car();
                o.setCarId(rs.getString("CAR_ID"));
                o.setCarName(rs.getString("CAR_NAME"));
                o.setCarNo(rs.getString("CAR_NO"));
                o.setCarType(rs.getInt("CAR_TYPE"));
                o.setStatus(rs.getInt("STATUS"));
                o.setCreateBy(rs.getString("CREATE_BY"));
                o.setCreateDate(new Date(rs.getDate("CREATE_DATE").getTime()));
                o.setUpdateBy(rs.getString("UPDATE_BY"));
                o.setUpdateDate(new Date(rs.getDate("UPDATE_DATE").getTime()));
                o.setCarTypeName(rs.getString("CAR_TYPE_NAME"));
                o.setStatusName(rs.getString("STATUS_NAME"));
                o.setCreateByName(rs.getString("CREATE_BY_NAME"));
                o.setUpdateByName(rs.getString("UPDATE_BY_NAME"));
                list.add(o);
            }
            rs.close();
            rs = null;
            stmt.close();
            stmt = null;
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                    conn = null;
                } catch (Exception ex) {
                }
            }
        }
        return list;
    }

    public int searchCount(int offset, Object[] certiria) {
        System.out.println("CarService search...");
        int count = 0;
        Connection conn = null;
        try {
            conn = ds.getConnection();
            StringBuilder s = new StringBuilder();
            s.append("SELECT COUNT(*) FROM car WHERE CAR_NAME LIKE ?");
            System.out.println("sql[" + s.toString() + "]");
            PreparedStatement stmt = conn.prepareStatement(s.toString());
            String name = (String) certiria[0];
            name = (name != null) ? name.replace('*', '%') : "";
            stmt.setString(1, name);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                count = rs.getInt(1);
            }
            rs.close();
            rs = null;
            stmt.close();
            stmt = null;
        } catch (Exception ex) {
            ex.printStackTrace();
        } finally {
            if (conn != null) {
                try {
                    conn.close();
                    conn = null;
                } catch (Exception ex) {
                }
            }
        }
        return count;
    }

    public Object findById(String carId) {
        
        StringBuilder s = new StringBuilder();
        s.append("SELECT ");
        s.append("IFNULL(A.CAR_ID, '-') AS CAR_ID,");
        s.append("IFNULL(A.CAR_NAME, '-') AS CAR_NAME,");
        s.append("IFNULL(A.CAR_NO, '-') AS CAR_NO,");
        s.append("IFNULL(A.CAR_TYPE, 0) AS CAR_TYPE,");
        s.append("IFNULL(A.STATUS, 0) AS STATUS,");
        s.append("IFNULL(A.CREATE_BY, '-') AS CREATE_BY,");
        s.append("IFNULL(A.CREATE_DATE, CURRENT_TIMESTAMP) AS CREATE_DATE,");
        s.append("IFNULL(A.UPDATE_BY, '-') AS UPDATE_BY,");
        s.append("IFNULL(A.UPDATE_DATE, CURRENT_TIMESTAMP) AS CREATE_DATE ");
        s.append("FROM car A WHERE A.CAR_ID = '");
        s.append(carId);
        s.append("'");
        System.out.println("sql["+ s.toString()+"]");
        Car o = null;
        try {
            Connection conn = ds.getConnection();
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(s.toString());
            if (rs.next()) {
                o = new Car();
                o.setCarId(rs.getString("CAR_ID"));
                o.setCarName(rs.getString("CAR_NAME"));
                o.setCarNo(rs.getString("CAR_NO"));
                o.setCarType(rs.getInt("CAR_TYPE"));
                o.setStatus(rs.getInt("STATUS"));
                o.setCreateBy(rs.getString("CREATE_BY"));
                o.setCreateDate(new Date(rs.getDate("CREATE_DATE").getTime()));
                o.setUpdateBy(rs.getString("UPDATE_BY"));
                o.setUpdateDate(new Date(rs.getDate("UPDATE_DATE").getTime()));
            }
            rs.close();
            if (rs != null) {
                rs = null;
            }

            stmt.close();
            if (stmt != null) {
                stmt = null;
            }

            conn.close();
            if (conn != null) {
                conn = null;
            }
            return o;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return o;
    }

    public boolean save(Object obj) {
        StringBuilder sql = new StringBuilder();
        sql.append("INSERT INTO car(CAR_ID,CAR_NAME,CAR_NO,CAR_TYPE,STATUS,CREATE_BY,CREATE_DATE,UPDATE_BY,UPDATE_DATE) ");
        sql.append("VALUES (?,?,?,?,?,?,CURRENT_TIMESTAMP,?,CURRENT_TIMESTAMP)");
        Car o = (Car) obj;
        try {
            Connection conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql.toString());
            stmt.setString(1, o.getCarId());
            stmt.setString(2, o.getCarName());
            stmt.setString(3, o.getCarNo());
            stmt.setInt(4, o.getCarType());
            stmt.setInt(5, o.getStatus());
            stmt.setString(6, o.getCreateBy());
            stmt.setString(7, o.getUpdateBy());
            stmt.executeUpdate();
            stmt.close();
            if (stmt != null) {
                stmt = null;
            }

            conn.close();
            if (conn != null) {
                conn = null;
            }
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }

    public boolean update(Object obj) {
        Car o = (Car) obj;
        StringBuilder sql = new StringBuilder();
        sql.append("UPDATE car ");
        sql.append("SET CAR_NAME=?,CAR_NO=?,CAR_TYPE=?,STATUS=?,UPDATE_BY=?,UPDATE_DATE=CURRENT_TIMESTAMP  ");
        sql.append("WHERE CAR_ID='");
        sql.append(o.getCarId());
        sql.append("' ");
        try {
            Connection conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql.toString());
            stmt.setString(1, o.getCarName());
            stmt.setString(2, o.getCarNo());
            stmt.setInt(3, o.getCarType());
            stmt.setInt(4, o.getStatus());
            stmt.setString(5, o.getUpdateBy());
            stmt.executeUpdate();
            stmt.close();
            if (stmt != null) {
                stmt = null;
            }

            conn.close();
            if (conn != null) {
                conn = null;
            }
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }

    @Override
    public boolean remove(Object oid) {
        String carId = (String) oid;
        StringBuilder sql = new StringBuilder();
        sql.append("DELETE FROM car WHERE CAR_ID = '");
        sql.append(carId.trim());
        sql.append("' ");
        try {
            Connection conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql.toString());
            stmt.executeUpdate();
            stmt.close();
            if (stmt != null) {
                stmt = null;
            }

            conn.close();
            if (conn != null) {
                conn = null;
            }
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }

    public boolean deleteBySQL(String sql) {
        try {
            Connection conn = ds.getConnection();
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.executeUpdate();
            stmt.close();
            conn.close();
        } catch (Exception ex) {
            ex.printStackTrace();
            return false;
        }
        return true;
    }
}
